﻿using System;
using System.Collections.Generic;
using System.Linq;
using UygulamaTakbisService.wsTakbisServis;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace UygulamaTakbisService
{
    public class IslemBilgiDao : IDisposable
    {
        TAKBISWebService takbisServis = new TAKBISWebService();

        public bool YevmiyeBilgiKaydet(YevmiyeBilgi yevmiyeBilgi)
        {
            bool sonuc;
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguLogConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = @"INSERT INTO YevmiyeBilgi with(updlock) (YevmiyeBilgiID,Kurum,Tarih,YevmiyeNo,KayitTarih) VALUES (@YevmiyeBilgiID,@Kurum,@Tarih,@YevmiyeNo,GETDATE())";

                        if (yevmiyeBilgi.YevmiyeBilgiID != null)
                            cmd.Parameters.Add("@YevmiyeBilgiID", yevmiyeBilgi.YevmiyeBilgiID);
                        else
                            cmd.Parameters.Add("@YevmiyeBilgiID", DBNull.Value);

                        if (yevmiyeBilgi.Kurum != null)
                            cmd.Parameters.Add("@Kurum", yevmiyeBilgi.Kurum);
                        else
                            cmd.Parameters.Add("@Kurum", DBNull.Value);

                        if (yevmiyeBilgi.Tarih != null)
                            cmd.Parameters.Add("@Tarih", yevmiyeBilgi.Tarih);
                        else
                            cmd.Parameters.Add("@Tarih", DBNull.Value);

                        if (yevmiyeBilgi.YevmiyeNo != null)
                            cmd.Parameters.Add("@YevmiyeNo", yevmiyeBilgi.YevmiyeNo);
                        else
                            cmd.Parameters.Add("@YevmiyeNo", DBNull.Value);

                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        cmd.ExecuteScalar();
                        transaction.Commit();
                        sonuc = true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        sonuc = false;
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return sonuc;
                }
            }
        }

        public bool YevmiyeBilgiKayitliMi(YevmiyeBilgi yevmiyeBilgi)
        {
            bool sonuc = false;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguLogConnection"].ToString()))
                {
                    string sql = string.Format(@"SELECT * 
                                                 FROM YevmiyeBilgi with(nolock) 
                                                 WHERE YevmiyeBilgiID = {0} and
                                                       Kurum like '{1}' and
                                                       day(Tarih) = {2} and
                                                       month(Tarih) = {3} and
                                                       year(Tarih) = {4} and
                                                       YevmiyeNo = {5}", yevmiyeBilgi.YevmiyeBilgiID, yevmiyeBilgi.Kurum, yevmiyeBilgi.Tarih.Value.Day, yevmiyeBilgi.Tarih.Value.Month, yevmiyeBilgi.Tarih.Value.Year, yevmiyeBilgi.YevmiyeNo);
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count >= 1)
                                sonuc = true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return sonuc;
        }

        public bool IslemBilgiKaydet(IslemBilgi islemBilgi)
        {
            bool sonuc;
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguLogConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = @"INSERT INTO IslemBilgi with(updlock) (IslemBilgiID,IslemTanimAd,BaslamaSekilAd,YevmiyeBilgiID,KayitTarih) VALUES (@IslemBilgiID,@IslemTanimAd,@BaslamaSekilAd,@YevmiyeBilgiID,GETDATE())";

                        if (islemBilgi.IslemBilgiID != null)
                            cmd.Parameters.Add("@IslemBilgiID", islemBilgi.IslemBilgiID);
                        else
                            cmd.Parameters.Add("@IslemBilgiID", DBNull.Value);

                        if (islemBilgi.IslemTanimAd != null)
                            cmd.Parameters.Add("@IslemTanimAd", islemBilgi.IslemTanimAd);
                        else
                            cmd.Parameters.Add("@IslemTanimAd", DBNull.Value);

                        if (islemBilgi.BaslamaSekilAd != null)
                            cmd.Parameters.Add("@BaslamaSekilAd", islemBilgi.BaslamaSekilAd);
                        else
                            cmd.Parameters.Add("@BaslamaSekilAd", DBNull.Value);

                        if (islemBilgi.Yevmiye != null)
                            cmd.Parameters.Add("@YevmiyeBilgiID", islemBilgi.Yevmiye.YevmiyeBilgiID);
                        else
                            cmd.Parameters.Add("@YevmiyeBilgiID", DBNull.Value);

                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        cmd.ExecuteScalar();
                        transaction.Commit();
                        sonuc = true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        sonuc = false;
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return sonuc;
                }
            }
        }

        public bool IslemBilgiKayitliMi(IslemBilgi islemBilgi)
        {
            bool sonuc = false;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguLogConnection"].ToString()))
                {
                    string sql = string.Format(@"SELECT * 
                                                 FROM IslemBilgi with(nolock) 
                                                 WHERE IslemBilgiID = {0} and
                                                       IslemTanimAd like '{1}' and
                                                       BaslamaSekilAd like '{2}' and
                                                       YevmiyeBilgiID = {3}", islemBilgi.IslemBilgiID, islemBilgi.IslemTanimAd, islemBilgi.BaslamaSekilAd, islemBilgi.Yevmiye.YevmiyeBilgiID);
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count >= 1)
                                sonuc = true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return sonuc;
        }

        public void Dispose()
        {
        }
    }
}
